##IMPORTANT: DELETE SITUATION NOT ACCOUNTED YET.
print 'records deleted will not be detected'
import pyodbc, os, logging, sys


def update_mdb(sql, v, db):

    conn_str = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;PWD=%s' % (db, PASSWORD)
    cnxn = pyodbc.connect(conn_str)
    c = cnxn.cursor()
    c.execute(sql, v)
    print sql
    print v
    print c.rowcount, ' updated.'
    cnxn.commit()
    print 'datebase updated'
     


class ConsoleHandler(logging.StreamHandler):
    """A handler that logs to console in the sensible way.

    StreamHandler can log to *one of* sys.stdout or sys.stderr.

    It is more sensible to log to sys.stdout by default with only error
    (logging.ERROR and above) messages going to sys.stderr. This is how
    ConsoleHandler behaves.
    """

    def __init__(self):
        logging.StreamHandler.__init__(self)
        self.stream = None # reset it; we are not going to use it anyway

    def emit(self, record):
        if record.levelno >= logging.ERROR:
            self.__emit(record, sys.stderr)
        else:
            self.__emit(record, sys.stdout)

    def __emit(self, record, strm):
        self.stream = strm
        logging.StreamHandler.emit(self, record)

    def flush(self):
        # Workaround a bug in logging module
        # See:
        #   http://bugs.python.org/issue6333
        if self.stream and hasattr(self.stream, 'flush') and not self.stream.closed:
            logging.StreamHandler.flush(self)


logger = logging.getLogger('my logger')
fh = logging.FileHandler('log.log')
fh.setLevel(logging.DEBUG)

ch = ConsoleHandler()
ch.setLevel(logging.INFO)
formatter = logging.Formatter('Line[%(lineno)d]: - %(message)s')
ch.setFormatter(formatter)
fh.setFormatter(formatter)
logger.addHandler(ch)
logger.addHandler(fh)
#global variable, password of the pipetech database
PASSWORD = 'sigchi'


#schema of the Runs table in the Pipetech database
#first item is the Primary key
schema_runs = ['RunID', 'O_&_M_Grading', 'GIS_Map', 'Additional_Info', 
              'Location_Code', 'Weather', 'Date_Cleaned', 
              'Pre-Cleaning', 'Sewer_Category', 'Purpose', 
              'Media_Label', 'Year_Renewed', 'Year_Laid', 
              'Length_Surveyed', 'Total_Length', 'Pipe_Joint_Length', 
              'Lining_Method', 'Material', 'Shape', 'Width', 
              'Height_(Diameter)', 'Flow_Control', 
              'Direction_of_Survey', 'Sewer_Use', 'Rim_to_Grade_(D)', 
              'Grade_to_Invert_(D)', 'Rim_to_Invert_(D)', 'Downstream_MH', 
              'Rim_to_Grade_(U)', 'Grade_to_Invert_(U)', 'Rim_to_Invert_(U)', 
              'Upstream_MH', 'Location_Details', 'City', 'Street', 
              'Time', 'Date', 'PIPETECHID', 'Pipe_Segment_Reference', 
              'PO_Number', 'Drainage_Area', 'Customer', 'Owner', 
              'Certificate_Number', 'Surveyor_Name', 
              'Structural_Grading', 'MPEGPath', 
              'DesignSlope', 'DiscName', 'EndFrame']
#schema of the Runs table in the Pipetech database
#first item is the PK
schema_details = [ 'DetailID', 'Measured_From_Up',
                   'Distance_To', 
                   'Joint_Number', 
                   'ConditionCode', 
                   'Comment', 
                   'Pass_Fail', 
                   'Grout_Volume', 
                   'Connection_Address', 
                   'Intrusion', 'Percentage', 
                   'Dimension2', 
                   'Dimension1', 
                   'Position_To', 
                   'Position_From', 
                   'Severity', 
                   'Continuous', 
                   'ModifierCode', 
                   'VideoFrameNumber', 
                    'Footage',
                   'MHID', 
                   'ConnectionID', 
                   'RunID'
                   ]

#global variable, schema of the pipetech database
SCHEMA = {'Runs': schema_runs, 'Details': schema_details}

def get_cursor(db, password):
    """
    A helper function to return a cursor to the MDB file
    """
    conn_str = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;PWD=%s' % (db, password)
    cnxn = pyodbc.connect(conn_str)
    return cnxn.cursor()

class Record(object):
    '''
    The Record class is used to compare the changes of a record.
    r = Record(100, {'a': 100, 'b': 200}
    r['a'] = 300
    r['b'] = 400
    print r.get_changes()
    
    '''
    def __init__(self, key=None, values={}, protected_fields=[]):
        self.values = values
        self._old = {}
        self.id = key
        self.protected_fields = protected_fields
    def __getitem__(self, name):
        return self.values.get(name)

    def __setitem__(self, name, value):
        """Log ticket modifications so the table ticket_change can be updated
        """
        if name in self.values and self.values[name] == value:
            return
        if name in self.protected_fields:
            return

        if name not in self._old: # Changed field
            #if it is a new value, return None
            self._old[name] = self.values.get(name)
        elif self._old[name] == value: # Change of field reverted
            del self._old[name]
        self.values[name] = value

    def get_changes(self):
        changes = []
        for name in self._old.keys():
            changes.append({'field': name, 'old': self._old[name], 'new': self.values[name]})
        return changes
    def __repr__(self):
        return '<record object>\nvalues:%s\nchanges:%s' % (str(self.values), str(self.get_changes()))
    


r = Record(100, {'a': 100, 'b': 200}, ['c'])
print r
r['a'] = 300
r['b'] = 400
r['c'] = 50
print r


class Run(object):
    '''
    get the run and defects from a pacp pipetech database
    '''
    def __init__(self, src, runid, password=PASSWORD, schema=SCHEMA):
        self.src = src
        self.password = password
        self.schema = schema
        self.runid = runid
        self.connection = get_cursor(self.src, self.password)
        self.getRun()
    def getRun(self):
        c = self.connection
        flds = ','.join(['"%s"' % x for x in self.schema['Runs']])
        sql = 'select %s from Runs where RunID=?' % (flds)
        row = c.execute(sql, [self.runid]).fetchone()
        result = {}
        if row:
            for fld in self.schema['Runs']:
                result[fld] = getattr(row, fld)
        self.run = Record(result['PIPETECHID'], result,['RunID', 'O_&_M_Grading', 'MPEGPath', 'EndFrame'])
    def getDetails(self):
        c = self.connection
        flds = ','.join(['"%s"' % x for x in self.schema['Details']])
        sql = 'select %s from Details where RunID=? order by Footage' % (flds)
        
        results = []
        for row in c.execute(sql, [self.runid]):
            result = {}
            for fld in self.schema['Details']:
                result[fld] = getattr(row, fld)
            results.append(Record(result['DetailID'], result,['RunID', 'DetailID']))
        return results
        
src = r'H:\PACP\cctv\DLZ\8-23-09 TO 8-29-09\KWILSON\0015S0428_0015S0428A\DLZ MK PACP.ptd'
r = Run(src, '1')
print r.run
print r.getDetails()


def validate_ptl(ptl_path):
    '''
    A ptl file is created based on individual ptd files. If they are placed in the same folder, the relative path
    for the mpeg file in the ptl file can be used to locate the ptd file.
    In this script, it compares the ptd files with the ptl file, if any difference is found, it is logged in the log.log file.
    '''
    logger.setLevel(logging.WARNING)
    logger.info('start validating')
    root = os.path.dirname(ptl_path)
    c = get_cursor(ptl_path, PASSWORD)
    flds = ','.join(['"%s"' % x for x in SCHEMA['Runs']])
    sql = 'select RunID from Runs'
    runs = []
    for row in c.execute(sql):
        runs.append(row.RunID)
    for runid in runs:
        logger.info('Comparing RunID=%s' % runid)
        result = {}
        ptl_run = Run(ptl_path, runid)
        mpeg_folder = os.path.dirname(ptl_run.run['MPEGPath'])
        ptd_path = os.path.join(root, os.path.join(mpeg_folder, 'DLZ MK PACP.ptd'))
        ptd_run = Run(ptd_path, 1)
        for fld in SCHEMA['Runs']:
            ptl_run.run[fld] = ptd_run.run[fld]
        #print ptl_run.run.get_changes()
        run_changes = ptl_run.run.get_changes()
        if os.path.dirname(ptd_run.run['MPEGPath'])=='':
            pass
        else:
            logger.warning(ptd_path)
            logger.warning('RunID[%s]: Wrong MPEG PATH: %s' % (runid,ptd_run.run['MPEGPath'] ))
            sql = 'update Runs set MPEGPath=? where RunID=?'
            update_mdb(sql, [os.path.basename(ptd_run.run['MPEGPath'])], ptd_path)
        if run_changes:
            for change in run_changes:
                sql = 'update Runs set "%s"=? where RunID=?' % change['field']
                update_mdb(sql, [change['old'], runid], ptd_path)
                logger.warning(ptd_path)
                logger.warning('RunID[%s]: %s: %s(ptl)=>%s(ptd)' % (runid, change['field'], change['old'], change['new']))
        else:
            logger.info('Run Check: OK')
        ptd_details = ptd_run.getDetails()
        ptl_details = ptl_run.getDetails()
        logger.info('    logging detail changes')
        if len(ptd_details)==len(ptl_details):
            for i, v in enumerate(ptl_details):
                for fld in SCHEMA['Details']:
                    v[fld] = ptd_details[i][fld]
            detail_changes = v.get_changes()
            if detail_changes:
                for change in detail_changes:
                    sql = 'update Details set "%s"=? where DetailID=?' % change['field']
                    update_mdb(sql, [change['old'], ptd_details[i]['DetailID']], ptd_path)
                    logger.warning(ptd_path)
                    logger.warning('DetailID[%s]: %s: %s(ptl)=>%s(ptd)' % (ptd_details[i]['DetailID'], change['field'], change['old'], change['new']))
                
            else:
                logger.info('Detail Check OK')
        else:
            logger.warning('    No of defects are different: %s(ptd) vs. %s(ptl)' % (len(ptd_details),len(ptl_details)))

ptl_path = r'H:\PACP\cctv\DLZ\DLZ MK PACP.ptl'
validate_ptl(ptl_path)
    
    

def folder_compare(folder1, folder2):
    """
    return a dictionary with all the source, raw data pairs:
    {pipetechid1: {'new': path, 'old': path},
      pipetechid2: {'new': path, 'old': path},
      ....}
    If no match is found, the value of 'new'/'old' is None.
      
      
    folder1, folder2 are two copies of the same data.
    fodler1 keeps updating, while folder2 keeps the original.
    
    The link between the two folders is established by,
    1. The inspection data are orgnized as:
    root
    -subfolder1
    --subfolder_level2
    ---subfolder_level...
    -----inspection folder(named after Pipetechid)
    -------DLZ MK PACP.ptd
    2. The algrithm:
      a. generate a dictionary of DLZ MK PACP.ptd files, {pipetechid: path}
      b. compare the two list based on the pipetechid
    """
    
      
    compare_list = {}
    
    for root, dirs, files in os.walk(folder1):
        for file in files:
            if file.lower()=='DLZ MK PACP.ptd'.lower():
                p = root.replace(folder1, '')
                #eg. H:\cctv\8-23-09 TO 8-29-09\KWILSON\0015S0428_0015S0428A
                # the last part is pipetechid
                ptid = p.split('\\')[-1]
                compare_list[ptid] = {'new': root, 'old': None}
    for root, dirs, files in os.walk(folder2):
        for file in files:
            if file.lower()=='DLZ MK PACP.ptd'.lower():
                p = root.replace(folder1, '')
                ptid = p.split('\\')[-1]
                if compare_list.has_key(ptid):
                    compare_list[ptid]['old'] = root
                else:
                    compare_list[ptid] = {'new': None, 'old': root}
    return compare_list
                

        
                

    
def dict_compare(d1, d2):
    """
    compare two dictionaries. Return a list of differences
    [{k, v1, v2},...]
    k: key
    v1: value in d1
    v2: value in d2
    'NSK': No such field, v1/v2 could be assigned a value of 'NSK'
    """
    results = []
    for k, v in d1.items():
        if d2.has_key(k):
            v1 = d1[k]
            v2 = d2[k]
            if v1==v2:
                pass
            else:
                results.append({'k': k, 'v1': v1, 'v2': v2})
        else:
            results.append({'k': k, 'v1': v1, 'v2': 'NSK'})
    for k, v in d2.items():
        if d1.has_key(k):
            pass
        else:
            results.append({'k': k, 'v1': 'NSK', 'v2': d2[k]})
    return results
            
            

def get_ptid(db):
    """
    A helper function to get the pipetechid
    """
    c = get_cursor(db, PASSWORD)
    sql = 'select PIPETECHID from Runs'
    ptid = c.execute(sql).fetchone()
    if ptid:
        return ptid[0]
    else:
        return None

def prettyprint(result):
    """
    return a formated text of the comparison
    """
    tmp = """
Pipetechid: %(pipetechid)s
source: %(source)s
   raw: %(raw)s
Table: Runs
%(runs_result)s
Table: Details
%(details_result)s
    """
    
    runs_result = ''
    for r in result['Runs']:
        runs_result += '  RunID-%s\n' % r['id']
        for y in r['result']:
            runs_result +=  "    %s changes from %s to %s\n" % (y['k'], y['v2'], y['v1'])
    details_result = ''
    for r in result['Details']:
        details_result += "  @%sft(ID:%s)\n" % (r['data']['Footage'], r['id'])
        for y in r['result']:
            details_result += "   %s changes from %s to %s\n" % (y['k'], y['v2'], y['v1'])
    result['runs_result'] =runs_result
    result['details_result'] =details_result
    return tmp % result

    
def db_compare(db1, db2, table_list):
    """
    Given two databases: db1, db2(path)
    a list of tables in the dbs: table_list
    return a dictionary of the compare result.
    
    {'raw': 'C:\\Documents and Settings\\ymeng\\Desktop\\test\\raw\\DLZ MK PACP.ptd',
    'source': 'C:\\Documents and Settings\\ymeng\\Desktop\\test\\cctv\\DLZ MK PACP.ptd',
      'pipetechid': u'0015S0428_0015S0428A', 
      'Runs': [{'data': {'Rim_to_Grade_(D)': 0.0}, 
              'id': 1, 
              'result': [{'v1': u'MEL', 'k': 'Surveyor_Name', 'v2': u'KWILSON'}, 
                      {'v1': u'mel_company', 'k': 'Customer', 'v2': u'DLZ'}]}], 
       'Details': [{'data': {'Severity': None}, 
               'id': 1, 
               'result': [{'v1': 100, 'k': 'MHID', 'v2': 0}]}]
               }
    
    """
    password = PASSWORD
    c1 = get_cursor(db1, password)
    c2 = get_cursor(db2, password)
    db_result = {}
    for tbl in table_list:
        tbl_result = []
        schema = SCHEMA[tbl]
        pk = schema[0]
        flds = '"' + '", "'.join(schema) + '"'
        sql = 'select %s from %s' % (flds, tbl)
        #Assuming there is only one run in each database
        for rr1 in c1.execute(sql).fetchall():
            r1 = dict(zip(schema, rr1))
            sql2 = 'select %s from %s where %s = %s' % (flds, tbl, pk, r1[pk])
            #matching record in db2
            rr = c2.execute(sql2).fetchone()
            if rr:
                r2 = dict(zip(schema, rr))
                result = dict_compare(r1, r2)
                if len(result)>0:
                    if result[0]['v1']=='NSK':
                        #r1 doesn't have the key
                        tbl_result.append({'id': r2[pk], 'data': r2, 'result': result})
                    else:
                        tbl_result.append({'id': r1[pk], 'data':r1, 'result': result})
            else:
                #No matching record in db2, which is a new one
                r0={}
                for k, v in r1.items():
                    r0[k] = None
                result = dict_compare(r1, r0)
                tbl_result.append({'id': r2[pk], 'data': r2, 'result': result})
            db_result[tbl] = tbl_result
    db_result['pipetechid'] = get_ptid(db1) 
    db_result['source'] = db1
    db_result['raw'] = db2
    
    return db_result


